package com.infocollect.common.utils.poi;


import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

/**
 * Excel相关处理
 * 
 * @author dongpj
 */
public class JxExcelUtil
{


    public static List readExcelToList(InputStream is, Map fieldNamesMap)throws BiffException, IOException {
        int sheetId = 0;
        List excelDataList = new ArrayList();
        try
        {
            WorkbookSettings workbookSettings=new WorkbookSettings();
            workbookSettings.setEncoding("GBK"); //关键代码，解决中文乱码
            Workbook book = Workbook.getWorkbook(is,workbookSettings);
            // 获得第一个工作表对象
            Sheet sheet = book.getSheet(sheetId);
            //现在，工作表已经可以获取到了，内容也可以获取到了，字段对应关系也已经有了，可以组数据了
            List fieldList = new ArrayList();//先组一个ＬＩＳＴ，用来存要取ＥＸＣＥＬ的哪些字段，这些字段分别在哪一列
            Iterator fieldit = fieldNamesMap.keySet().iterator();
            while(fieldit.hasNext())
            {
                String fieldName = (String)fieldit.next();
                String fieldExcelName = (String)fieldNamesMap.get(fieldName);

                //System.out.println(fieldName+"------"+fieldExcelName);
                Map fieldMap = new HashMap();
                fieldMap.put("FIELDNAME",fieldName);
                fieldMap.put("FIELDEXCELNAME",fieldExcelName);
                for(int excelColumnCount = 0;excelColumnCount<sheet.getColumns();excelColumnCount++)
                {
                    Cell fieldCell =  sheet.getCell(excelColumnCount,0);
                    String excelName =fieldCell.getContents();
                    if(fieldExcelName.equals(excelName))
                    {
                        fieldMap.put("FIELDEXCELCOLUMNCOUNT",String.valueOf(excelColumnCount));
                        fieldList.add(fieldMap);
                        break;
                    }
                }
            }
            int sheetRow = sheet.getRows();
            //System.out.println("fieldList:");
            //System.out.println(fieldList);
            for(int excelRow = 1;excelRow<sheet.getRows();excelRow++)//好了，可以一行行取数据了
            {
                Map excelDataMap = new HashMap();
                for(int columnRow = 0;columnRow<fieldList.size();columnRow++)
                {
                    Map fieldMap = (Map)fieldList.get(columnRow);
                    int excelColumnCount = Integer.parseInt((String)fieldMap.get("FIELDEXCELCOLUMNCOUNT"));
                    ///getCell 先列后行
                    Cell fieldCell =  sheet.getCell(excelColumnCount,excelRow);
                    String fieldName = (String)fieldMap.get("FIELDNAME");
                    excelDataMap.put(fieldName,fieldCell.getContents());
                }
                excelDataList.add(excelDataMap);
            }
        }catch(Exception ex)
        {
            ex.printStackTrace();
        }
        return excelDataList;
    }
}